package com.dao;

import com.model.Build;
import com.model.PageBean;
import com.model.Room;
import com.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author wangjg
 * @date 2023/11/1 15:15
 * @Description: 宿舍管理DAO
 */
public class RoomDao {

    public List<Room> roomList(Connection con, PageBean pageBean, Room room)throws Exception {
        List<Room> roomList = new ArrayList<>();
        StringBuilder sb = new StringBuilder("select * from t_room t1 where 1=1");
        if(StringUtil.isNotEmpty(room.getNum())) {
            sb.append(" and t1.num like '%").append(room.getNum()).append("%'");
        }
        if(null != room.getBuildId()) {
            sb.append(" and t1.buildId = ").append(room.getBuildId());
        }
        if(pageBean != null) {
            sb.append(" limit ").append(pageBean.getStart()).append(",").append(pageBean.getPageSize());
        }
        PreparedStatement preparedStatement = con.prepareStatement(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while(rs.next()) {
            Room ro = new Room();
            ro.setId(rs.getInt("id"));
            ro.setBuildId(rs.getInt("buildId"));
            ro.setBuildName(rs.getString("buildName"));
            ro.setNum(rs.getString("num"));
            ro.setCapacity(rs.getString("capacity"));
            ro.setStatus(rs.getString("status"));
            roomList.add(ro);
        }
        return roomList;
    }

    public Room roomInfo(Connection con, int id)throws Exception {
        String sql = "select * from t_room where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()) {
            Room room = new Room();
            room.setId(rs.getInt("id"));
            room.setBuildId(rs.getInt("buildId"));
            room.setBuildName(rs.getString("buildName"));
            room.setNum(rs.getString("num"));
            room.setCapacity(rs.getString("capacity"));
            room.setStatus(rs.getString("status"));
            return room;
        }
        return null;
    }

    public int roomCount(Connection con, Room room)throws Exception {
        StringBuilder sb = new StringBuilder("select count(*) as total from t_room t1");
        if(StringUtil.isNotEmpty(room.getNum())) {
            sb.append(" where t1.floor like '%").append(room.getNum()).append("%'");
        }
        if(null != room.getBuildId()) {
            sb.append(" where t1.buildId = ").append(room.getBuildId());
        }
        PreparedStatement preparedStatement = con.prepareStatement(sb.toString());
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()) {
            return rs.getInt("total");
        } else {
            return 0;
        }
    }


    public int addRoom(Connection con, Room room)throws Exception {
        String sql = "insert into t_room values(null,?,?,?,?,?)";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, room.getBuildId());
        preparedStatement.setString(2, room.getBuildName());
        preparedStatement.setString(3, room.getNum());
        preparedStatement.setString(4, room.getCapacity());
        preparedStatement.setString(5, room.getStatus());
        return preparedStatement.executeUpdate();
    }

    public int deleteRoom(Connection con, String id)throws Exception {
        String sql = "delete from t_room where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setString(1, id);
        return preparedStatement.executeUpdate();
    }

    public int updateRoom(Connection con, Room room)throws Exception {
        String sql = "update t_room set buildId=?,buildName=?,num=? ,capacity=?,status=? where id=?";
        PreparedStatement preparedStatement = con.prepareStatement(sql);
        preparedStatement.setInt(1, room.getBuildId());
        preparedStatement.setString(2, room.getBuildName());
        preparedStatement.setString(3, room.getNum());
        preparedStatement.setString(4, room.getCapacity());
        preparedStatement.setString(5, room.getStatus());
        preparedStatement.setInt(6, room.getId());
        return preparedStatement.executeUpdate();
    }
}
